package org.hgvc.webapp.ets.spring.dao;

import java.util.Date;
import java.util.List;

import javax.faces.application.Application;

import org.hgvc.webapp.ets.bo.Agent;
import org.hgvc.webapp.ets.bo.Branch;
import org.hgvc.webapp.ets.bo.Calendar;
import org.hgvc.webapp.ets.bo.CalendarDtl;
import org.hgvc.webapp.ets.bo.Country;
import org.hgvc.webapp.ets.bo.Currency;
import org.hgvc.webapp.ets.bo.CurrencyRate;
import org.hgvc.webapp.ets.bo.DocControl;
import org.hgvc.webapp.ets.bo.DownPaymentInstallmentSchema;
import org.hgvc.webapp.ets.bo.HotelRciResort;
import org.hgvc.webapp.ets.bo.HotelResort;
import org.hgvc.webapp.ets.bo.HotelRoomType;
import org.hgvc.webapp.ets.bo.InstallmentSchema;
import org.hgvc.webapp.ets.bo.Nationality;
import org.hgvc.webapp.ets.bo.PackagePeakRates;
import org.hgvc.webapp.ets.bo.Product;
import org.hgvc.webapp.ets.bo.Race;
import org.hgvc.webapp.ets.bo.Remark;
import org.hgvc.webapp.ets.bo.Salutation;
import org.hgvc.webapp.ets.bo.State;
import org.hgvc.webapp.ets.bo.Status;
import org.hgvc.webapp.ets.bo.Territory;
import org.hgvc.webapp.ets.bo.TransactionType;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hgvc.webapp.ets.constant.PartnerConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;
import org.jocsb.base.util.SpringUtils;


public class MasterSetupHbmDao extends AbstractHibernateDao{

	
	public List<Country> getCountryByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select country from Country country where country.isDeleted=:value and (country.code=:code or country.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Country>)query.list();
	}
	
	public List<State> getStatesByCountryId(String countryId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select state from State state where state.isDeleted=:value and state.country.countryId=:countryId");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("countryId", countryId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<State>)query.list();
	}
	
	public List<State> getStateByCodeOrDesc(String code,String desc,String countryId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select state from State state where state.isDeleted=:value and state.country.countryId=:countryId and(state.code=:code or state.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("countryId", countryId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<State>)query.list();
	}
	
	public List<Salutation> getSalutationByDesc(String desc,String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select salutation from Salutation salutation where salutation.isDeleted=:value and ( salutation.desc=:desc or salutation.code=:code)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("desc", desc);
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Salutation>)query.list();
	}
	
	public List<Race> getRaceByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select race from Race race where race.isDeleted=:value and (race.code=:code or race.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Race>)query.list();
	}
	
	public List<Branch> getBranchByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select branch from Branch branch where branch.isDeleted=:value and (branch.code=:code or branch.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Branch>)query.list();
	}
	
	public List<Agent> getAgentByCodeOrDescOrEmail(String code,String desc,String email)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select agent from Agent agent where agent.isDeleted=:value and (agent.code=:code or agent.desc=:desc or agent.email=:email)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("email", email);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Agent>)query.list();
	}
	
	public List<Status> getStatusByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select status from Status status where status.isDeleted=:value and (status.code=:code or status.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Status>)query.list();
	}
	
	public List<TransactionType> getTransactionTypeByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select type from TransactionType type where type.isDeleted=:value and (type.code=:code or type.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<TransactionType>)query.list();
	}
	
	public List<Product> getProductByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select product from Product product where product.isDeleted=:value and (product.code=:code or product.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Product>)query.list();
	}
	
	public List<HotelResort> getHotelResortByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select hr from HotelResort hr where hr.isDeleted=:value and (hr.code=:code or hr.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<HotelResort>)query.list();
	}
	
	public List<HotelRciResort> getHotelRciResortByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select hr from HotelRciResort hr where hr.isDeleted=:value and (hr.code=:code or hr.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<HotelRciResort>)query.list();
	}
	
	public List<HotelRoomType> getHotelRoomTypeByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select rm from HotelRoomType rm where rm.isDeleted=:value and (rm.code=:code or rm.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<HotelRoomType>)query.list();
	}
	
	public List<Currency> getCurrencyByCodeOrDesc(String code,String desc)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select currency from Currency currency where currency.isDeleted=:value and (currency.code=:code or currency.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Currency>)query.list();
	}
	
	
	
	public List<Territory> getTerritoryByCodeOrDesc(String code,String desc,String stateId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select t from Territory t where t.isDeleted=:value and t.state.stateId=:stateId and(t.code=:code or t.desc=:desc)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("stateId", stateId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Territory>)query.list();
	}
	
	public List<Territory> getTerritoryByStateId(String stateId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select t from Territory t where t.isDeleted=:value and t.state.stateId=:stateId");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("stateId", stateId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Territory>)query.list();
	}
	
	public List<CurrencyRate> getCurrencyRateListByCurrencyId(Currency currency)throws Exception{
		Criteria criteria = getSession().createCriteria(CurrencyRate.class);
		criteria.add(Restrictions.eq("currency", currency));
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		List<CurrencyRate> list=criteria.list();
		return list;
	}
	
	public DocControl getDocControlByType(String type)throws Exception{
		Criteria criteria=getSession().createCriteria(DocControl.class);
		criteria.add(Restrictions.eq("type", type));
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		criteria.setMaxResults(1);
		DocControl docControl=null;
		List<DocControl> list=criteria.list();
		if (list.size()>0)
			docControl = list.get(0);
		return docControl;
	}
	
	public List<Territory> getTerritoryByStateId()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select t from Territory t where t.isDeleted=:value ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Territory>)query.list();
	}
	
	public List<Calendar> getCalendarByCodeOrDesc(String code,String desc,Date date)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select calendar from Calendar calendar where calendar.isDeleted=:value and (calendar.code=:code or calendar.desc=:desc)");
		str.append(" and calendar.date=:date");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("desc", desc);
		query.setParameter("date", date);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Calendar>)query.list();
	}
 
	public List<CalendarDtl> getCalendarDtlByDateType(String calendarCatId, Date fromDate, Date toDate) throws Exception {
		StringBuffer str = new StringBuffer();
		str.append("select calendar from CalendarDtl calendar where calendar.isDeleted=:isDeleted " +
				" and ( :fromDate<=calendar.calDate and calendar.calDate<=:toDate and calendar.type=:dateType) or calendar.type=:dayType");
		str.append(" and calendar.calCat.calendarCatId=:calendarCatId");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("dateType", PartnerConstant.PARTNER_CAL_TYPE_DATE);
		query.setParameter("dayType", PartnerConstant.PARTNER_CAL_TYPE_DAY);
		query.setParameter("calendarCatId", calendarCatId);
		query.setParameter("fromDate", fromDate);
		query.setParameter("toDate", toDate);
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		return (List<CalendarDtl>) query.list();
	}
 
	public List<PackagePeakRates> getPackagePeakRates(String packageId, String peakType) throws Exception {
		StringBuffer str = new StringBuffer();
		str.append("select packagePeakRates from PackagePeakRates packagePeakRates where packagePeakRates.isDeleted=:isDeleted " +
				" and packagePeakRates.packages.packageId=:packageId" +
				" and packagePeakRates.peakType=:peakType");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("packageId", packageId);
		query.setParameter("peakType", peakType);
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		return (List<PackagePeakRates>) query.list();
	}
	
	public List<InstallmentSchema> getInstallmentSchemaByCodeOrName(String code,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select schema from InstallmentSchema schema where schema.isDeleted=:value and (schema.code=:code or schema.name=:name)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<InstallmentSchema>)query.list();
	}
	
	public List<DownPaymentInstallmentSchema> getDownPaymentInstallmentSchemaByCodeOrName(String code,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select schema from DownPaymentInstallmentSchema schema where schema.isDeleted=:value and (schema.code=:code or schema.name=:name)");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<DownPaymentInstallmentSchema>)query.list();
	}
	
	public List<Remark> getRemarkList()throws Exception{
		Criteria criteria=getSession().createCriteria(Remark.class);
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		List<Remark> list=criteria.list();
		return list;
	}
	
	public List<Nationality> getNationalityList()throws Exception{
		StringBuffer strBuf = new StringBuffer();
		strBuf.append("select n from Nationality n ");
		strBuf.append("where n.isDeleted=:isDeleted");
		
		Query query = getSession().createQuery(strBuf.toString());
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		
		List<Nationality> list = query.list();
		return list;
	}
}
